﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Public;


namespace Dal
{
    public class Product
    {
        public static DataTable MULTIPLE(string strCon)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_MULTIPLE");
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static DataTable MULTIPLE(string strCon, int serId) 
        {
            DataTable dt = new DataTable();
            SqlParameter[] pars = { new SqlParameter("@SerId", serId) };
            dt.Locale = CultureInfo.InvariantCulture;
            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_MULTIPLE_SerID", pars);
            }
            catch (Exception)
            {
                dt = null;
            }
            return dt;
        }

        public static DataGroup MULTIPLE(string strCon, int pageSize, int pageIndex, string whereStr)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            int countRow = 0;
            int countPage = 0;
            SqlParameter[] pars = new SqlParameter[5];
            pars[0] = new SqlParameter("@PageSize", SqlDbType.Int);
            pars[0].Value = pageSize;
            pars[1] = new SqlParameter("@PageIndex", SqlDbType.Int);
            pars[1].Value = pageIndex;
            pars[2] = new SqlParameter("@WhereStr", SqlDbType.NVarChar, 1000);
            pars[2].Value = whereStr;
            pars[3] = new SqlParameter("@CountRow", SqlDbType.Int);
            pars[3].Direction = ParameterDirection.Output;
            pars[4] = new SqlParameter("@CountPage", SqlDbType.Int);
            pars[4].Direction = ParameterDirection.Output;
            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_Search_MULTIPLE", pars);
                countRow = System.Convert.ToInt32(pars[3].Value, CultureInfo.InvariantCulture);
                countPage = System.Convert.ToInt32(pars[4].Value, CultureInfo.InvariantCulture);
            }
            catch
            {
                countRow = 0;
                countPage = 0;
                throw;
            }
            return new DataGroup(dt, countRow, countPage);
        }

        public static DataTable Single(string strCon, string urlname)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@UrlName", SqlDbType.NVarChar, 1000);
            pars[0].Value = urlname;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_SINGLE_URL", pars);
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static DataTable Single(string strCon, int pkid)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@PKID", SqlDbType.Int);
            pars[0].Value = pkid;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_SINGLE_PKID", pars);
            }
            catch
            {
                throw;
            }
            return dt;
        }


        public static int Update(string strCon,
                string txbProName,
                string txbKeywords,
                string txbDescription,
                string txbProCode,
                string txbThumbURL,
                string txbImgURL,
                int txbCatID,
                int txbSerID,
                string txbProContent,
                int txbSortBy,
                int txbPKID)
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[11];
            pars[0] = new SqlParameter("@ProName", SqlDbType.NVarChar, 50);
            pars[0].Value = txbProName;
            pars[1] = new SqlParameter("@Keywords", SqlDbType.NVarChar);
            pars[1].Value = txbKeywords;
            pars[2] = new SqlParameter("@Description", SqlDbType.NVarChar);
            pars[2].Value = txbDescription;
            pars[3] = new SqlParameter("@ProCode", SqlDbType.NVarChar,50);
            pars[3].Value = txbProCode;
            pars[4] = new SqlParameter("@ThumbURL", SqlDbType.NVarChar);
            pars[4].Value = txbThumbURL;
            pars[5] = new SqlParameter("@ImgURL", SqlDbType.NVarChar);
            pars[5].Value = txbImgURL;
            pars[6] = new SqlParameter("@CatID", SqlDbType.Int);
            pars[6].Value = txbCatID;
            pars[7] = new SqlParameter("@SerID", SqlDbType.Int);
            pars[7].Value = txbSerID;
            pars[8] = new SqlParameter("@ProContent", SqlDbType.NVarChar);
            pars[8].Value = txbProContent;
            pars[9] = new SqlParameter("@SortBy", SqlDbType.TinyInt);
            pars[9].Value = txbSortBy;
            pars[10] = new SqlParameter("@PKID", SqlDbType.Int);
            pars[10].Value = txbPKID;

            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_UPDATE", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int Update(string strCon,Model.Product pro) 
        {
            int rtn = 0;
            SqlParameter[] pars = { 
                                    new SqlParameter("@PKID",pro.PKID),
                                    new SqlParameter("@ProName",pro.ProName),
                                    new SqlParameter("@Keywords",pro.Keywords),
                                    new SqlParameter("@Summary",pro.Summary),
                                    new SqlParameter("@Description",pro.Description),
                                    new SqlParameter("@ProCode",pro.ProCode),
                                    new SqlParameter("@MainImgURL",pro.MainImgURL),
                                    new SqlParameter("@BigImgURL",pro.BigImgURL),
                                    new SqlParameter("@MediumImgURL",pro.MediumImgURL),
                                    new SqlParameter("@PackInfo",pro.PackInfo),
                                    new SqlParameter("@CatID",pro.CatId),
                                    new SqlParameter("@SerID",pro.SerId),
                                    new SqlParameter("@ProContent",pro.ProContent),
                                    new SqlParameter("@ProTypeNum",pro.ProTypeNum),
                                    new SqlParameter("@SortBy",pro.SortBy)
                                  };
            try
            {
                rtn = SqlHelper.ExecuteNonQuery(Public.DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_UPDATE", pars);
            }
            catch (Exception)
            {
                throw;
            }
            return rtn;
        }

        public static int Insert(string strCon,Model.Product pro)
        {
            int rtn = 0;
            SqlParameter[] pars = { 
                                    new SqlParameter("@ProName",pro.ProName),
                                    new SqlParameter("@Keywords",pro.Keywords),
                                    new SqlParameter("@Summary",pro.Summary),
                                    new SqlParameter("@Description",pro.Description),
                                    new SqlParameter("@ProCode",pro.ProCode),
                                    new SqlParameter("@MainImgURL",pro.MainImgURL),
                                    new SqlParameter("@BigImgURL",pro.BigImgURL),
                                    new SqlParameter("@MediumImgURL",pro.MediumImgURL),
                                    new SqlParameter("@PackInfo",pro.PackInfo),
                                    new SqlParameter("@CatID",pro.CatId),
                                    new SqlParameter("@SerID",pro.SerId),
                                    new SqlParameter("@ProContent",pro.ProContent),
                                    new SqlParameter("@ProTypeNum",pro.ProTypeNum),
                                    new SqlParameter("@SortBy",pro.SortBy)
                                  };

            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_INSERT", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int INSERT(string strCon,string txbProName,
                string txbKeywords,
                string txbDescription,
                string txbProCode,
                string txbThumbURL,
                string txbImgURL,
                int txbCatID,
                int txbSerID,
                string txbProContent,
                int txbSortBy
            )
        {

            int rtn = 0;
            SqlParameter[] pars = { 
                                    new SqlParameter("@ProName",txbProName),
                                    new SqlParameter("@Keywords",txbKeywords),
                                    new SqlParameter("@Description",txbDescription),
                                    new SqlParameter("@ProCode",txbProCode),
                                    new SqlParameter("@ThumbURL",txbThumbURL),
                                    new SqlParameter("@ImgURL",txbImgURL),
                                    new SqlParameter("@CatID",txbCatID),
                                    new SqlParameter("@SerID",txbSerID),
                                    new SqlParameter("@ProContent",txbProContent),
                                    new SqlParameter("@SortBy",txbSortBy)
                                  };
            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_INSERT", pars);
            }
            catch (Exception)
            {
                throw;
            }
            return rtn;
        }

        public static int Delete(string strCon, int pkid)
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@PKID", SqlDbType.SmallInt);
            pars[0].Value = pkid;
            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Product_DELETE", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

    }
}
